package com.stu.service.impl;

import cn.hutool.core.util.StrUtil;
import com.stu.po.Class;
import com.stu.po.College;
import com.stu.po.Major;
import com.stu.po.Students;
import com.stu.service.*;
import com.stu.tools.ReData;
import com.stu.vo.ExcelData;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Service
public class ExcelServiceImpl implements ExcelService {
    @Autowired
    ClassService classService;
    @Autowired
    StudentsService studentsService;
    @Autowired
    CollegeService collegeService;
    @Autowired
    MajorService majorService;
    @Resource(name = "dataSource")
    private DataSource dataSource;

    private List<Class> classLists;
    private List<College> collegeLists;
    private List<Major> majorLists;
    private List<Integer> stuIdLists;

    @Transactional(rollbackFor = Exception.class)
    public void stuAdd(List<Students> list) throws SQLException {
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        String sql = "INSERT INTO students (stu_id,ID,name,pwd,register_time,class_id,major_id,college_id,status) VALUES(?,?,?,?,?,?,?,?,?) ";
        PreparedStatement statement = connection.prepareStatement(sql);
        for (Students s : list) {
            statement.setInt(1, s.getStuId());
            statement.setString(2, s.getID());
            statement.setString(3, s.getID().substring(s.getID().length() - 4));
            statement.setString(4, s.getName());
            statement.setDate(5, new Date(s.getRegisterTime().getTime()));
            statement.setInt(6, s.getClassId());
            statement.setInt(7, s.getMajorId());
            statement.setInt(8, s.getCollegeId());
            statement.setInt(9, s.getStatus());
            statement.addBatch();
        }
//        long start = System.currentTimeMillis();
        statement.executeBatch();
        connection.commit();
        statement.close();
        connection.close();

//        System.out.println(System.currentTimeMillis() - start);
    }


    public void test() {
        System.out.println("开始调用");
    }


    public ReData dataSynthesis(List<ExcelData> list) throws Exception {
        List<Students> studentsList = new ArrayList<>();
        List<Integer> inList = studentsService.stuIdList();
        List<Integer> outList = new ArrayList<>();
        for (ExcelData d : list) {
            if (outList != null && !ListComparison(outList, d.getStuId())) {
                continue;
            }
            if (!ListComparison(inList, d.getStuId())) {
                continue;
            } else {
                outList.add(d.getStuId());
            }

//            Integer collegeId = collegeService.CollegeByName(d.getCollegeName());
//            Integer majorId = majorService.MajorByName(d.getMajorName(), d.getCollegeName());
//            Integer classId = classService.ClassByName(d.getClassName(),majorId);

            Integer collegeId = getCollege(d.getCollegeName());
            Integer majorId = getMajor(d.getMajorName(), collegeId);
            Integer classId = getClass(d.getClassName(), majorId);
//            System.out.println(collegeId+""+classId+""+classId);
            if (classId == null || collegeId == null || majorId == null || StrUtil.isBlank(d.getID()) || StrUtil.isBlank(d.getName())) {
                continue;
            }
            Students s = new Students();
            s.setName(d.getName());
            s.setID(d.getID());
            s.setRegisterTime(d.getRegisterTime());
            s.setStatus(getState(d.getState()));
            s.setStuId(d.getStuId());
            s.setClassId(classId);
            s.setCollegeId(collegeId);
            s.setMajorId(majorId);
            studentsList.add(s);
        }
        outList.clear();
        inList.clear();
        if (stuIdLists != null) {
            stuIdLists.clear();
        }
        return ReData.getTrue("", studentsList);
    }

    public Integer getState(String status) {
        Integer state = null;
        switch (status) {
            case "在校":
                state = 0;
                break;
            case "休学":
                state = 1;
                break;
            case "辍学":
                state = 2;
                break;
            default:
                state = 0;
                break;
        }
        return state;
    }

    public boolean ListComparison(List<Integer> list, Integer stuId) {
        boolean x = true;
        for (Integer i : list) {
            if (Objects.equals(i, stuId)) {
                x = false;
                break;
            }
        }
        return x;
    }

    public void dataCheck(ExcelData data) throws Exception {
        stuIdCheck(data.getStuId());
        collegeCheck(data.getCollegeName());
        majorCheck(data.getMajorName());
        classCheck(data.getClassName());
    }

    public void classCheck(String data) throws Exception {
        if (classLists == null) {
            classLists = classService.classList();
        }
        int i = 0;
        for (Class cs : classLists) {
            if (cs.getClassName().equals(data)) {
                i++;
            }
        }
        if (i == 0) {
            throw new Exception();
        }
    }

    public void collegeCheck(String data) throws Exception {
        if (collegeLists == null) {
            collegeLists = collegeService.collegeList();
        }
        int i = 0;
        for (College ce : collegeLists) {
            if (ce.getCollegeName().equals(data)) {
                i++;
            }
        }
        if (i == 0) {
            throw new Exception();
        }
    }

    public void majorCheck(String data) throws Exception {
        if (majorLists == null) {
            majorLists = majorService.majorList();
        }
        int i = 0;
        for (Major mr : majorLists) {
            if (mr.getMajorName().equals(data)) {
                i++;
            }
        }
        if (i == 0) {
            throw new Exception();
        }
    }

    public void stuIdCheck(Integer data) throws Exception {
        if (stuIdLists == null) {
            stuIdLists = studentsService.stuIdList();
        }
        int i = 0;
        for (Integer s : stuIdLists) {
            if (s.equals(data)) {
                i++;
            }
        }
        if (i > 0) {
            throw new Exception();
        }
    }

    @Override
    public Integer getCollege(String collegeName) throws Exception {

        if (collegeLists == null) {
            collegeLists = collegeService.collegeList();
        }
        for (College ce : collegeLists) {
            if (ce.getCollegeName().equals(collegeName)) {
                return ce.getCollegeId();
            }
        }
        return null;
    }

    @Override
    public Integer getMajor(String majorName, Integer collegeId) throws Exception {
        if (majorLists == null) {
            majorLists = majorService.majorList();
        }
        for (Major mr : majorLists) {
            if (mr.getMajorName().equals(majorName) && mr.getCollegeId() == collegeId) {
                return mr.getMajorId();
            }
        }
        return null;
    }

    @Override
    public Integer getClass(String className, Integer majorId) throws Exception {
        if (classLists == null) {
            classLists = classService.classList();
        }
        for (Class cs : classLists) {
            if (cs.getClassName().equals(className) && cs.getMajorId() == majorId) {
                return cs.getClassId();
            }
        }
        return null;
    }
}
